

*********************************************************************************
*********************************************************************************
*** Assemble new accounts daily window dataset from and Family Search records ***
*********************************************************************************
*********************************************************************************
clear
use working/sequential_records_with_geography
* Merge to "valid_bank_dates" to get all dates (including those which may not have a sequential record, i.e. no account record that day)
merge m:1 city date using working/valid_bank_dates, assert(match using) nogen
sort city date

* Explicitly assigning 0/1 ensures dates with no information will be 0

collapse (sum) new_account new_local_account new_white_account new_nonlocal_account (first) _temp = booknum, by(city date date_seq_no)
qui duplicates report city date
assert r(N) == r(unique_value)
egen first_acct_in_block = min(_temp), by(date_seq_no)
drop _temp

la var new_account "New accounts opened"
la var new_local_account "New local account opened"
la var new_white_account "New white account opened"

merge m:1 city using data/branch_data, keep(match) assert(match using) nogen


label define bank_branch 1 `"Alexandria"'  2 `"Atlanta"'  3 `"Augusta"'  4 `"Baltimore"'  5 `"Beaufort"'  6 `"Charleston"'  7 `"Columbia"'  8 `"Columbus"'  9 `"Huntsville"'  10 `"Jacksonville"'  11 `"Lexington"'  12 `"Little Rock"'  13 `"Louisville"'  14 `"Lynchburg"'  15 `"Macon"'  16 `"Memphis"'  17 `"Mobile"'  18 `"Nashville"'  19 `"Natchez"'  20 `"New Bern"'  21 `"New Orleans"'  22 `"New York"'  23 `"Norfolk"'  24 `"Philadelphia"'  25 `"Raleigh"'  26 `"Richmond"'  27 `"Savannah"'  28 `"Shreveport"'  29 `"St. Louis"'  30 `"Tallahassee"'  31 `"Vicksburg"'  32 `"Washington"'  33 `"Wilmington"', replace

encode city, g(bank) label(bank_branch) noextend 
la var bank "Bank branch"

tsset bank date
save working/fs_accounts_per_day_demographics, replace


* Create seperate datasets around data windows
capture program drop window_maker
program window_maker
	preserve
	local event_date = `1' 
	local savefile = "`2'"
	* Keep only dates in the valid sequence which spans the event date
	by bank (date): egen event_seq = total(cond(date==`event_date',date_seq_no,0))
	by bank: keep if event_seq == date_seq_no
	qui duplicates report bank
	local a = r(unique_value)
	qui duplicates report bank date_seq_no
	assert `a' ==  r(unique_value)

	* Drop if dates do not span the event
	by bank (date): drop if date[1]>=`event_date' | date[_N] <=`event_date'

	* For convenience retain only a maximum event window
	keep if inrange(date, `event_date' - 120, `event_date' + 120)

	
	gen long event_time = date - `event_date'
	by bank (date): gen fdate = date[1]
	by bank (date): gen ldate = date[_N]
	by bank (date): gen long max_days_before = `event_date' - fdate
	by bank (date): gen long max_days_after = ldate - `event_date'
	
	gen long event_date = `event_date'
	gen byte post_event_date = date > `event_date'
	
	format ldate fdate event_date %td
	
	la var fdate "First available date"
	la var ldate "Last available date"
	la var max_days_before "# of days available before event"
	la var max_days_after "# of days available after event"
	la var event_time "Event time"
	la var post_event_date "Post Event"
	la var event_date "Event Date"
	gen event = "`3'"

	* Generate distance
	if !mi("`4'`5'") {
	vincenty lat lon `4' `5', vin(dist_to_event)
	}
	qui compress
	tsset bank date
	save "`2'", replace
	restore
end

use working/fs_accounts_per_day_demographics
window_maker mdy(09,18,1873) "working/jay_cooke_window_demographics"  "Jay Cooke Failure"
window_maker mdy(9,18,1873) "working/stuctural_break_w1_window"  "Structural Break 1"
window_maker mdy(12,25,1873) "working/stuctural_break_w2_window"  "Structural Break 2"
window_maker mdy(4,1,1874) "working/stuctural_break_w3_window"  "Structural Break 3"




use working/jay_cooke_window_demographics, clear
keep city date new_account new_local_account new_white_account new_nonlocal_account
rename new_account new_account_check
drop if city=="Philadelphia"
save working/jay_cooke_window_demographics, replace

